﻿using System.Globalization;
using System.Reflection;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Caching.Distributed;
using Microsoft.Extensions.Logging;
using OfficeOpenXml;

namespace Devonline.AspNetCore;

/// <summary>
/// excel 导入导出操作的服务
/// </summary>
/// <typeparam name="TDbContext">数据库上下文</typeparam>
/// <typeparam name="TKey">键类型</typeparam>
public class ExcelImportService<TDbContext, TKey> : ServiceBase<TDbContext, TKey>, IExcelImportService<TDbContext, TKey> where TDbContext : DbContext where TKey : IConvertible
{
    private readonly ExcelPackage _excelPackage;

    /// <summary>
    /// 构造函数
    /// </summary>
    /// <param name="context"></param>
    /// <param name="logger"></param>
    /// <param name="cache"></param>
    /// <param name="httpContextAccessor"></param>
    /// <param name="appSetting"></param>
    public ExcelImportService(TDbContext context, ILogger<ExcelImportService<TDbContext, TKey>> logger, IDistributedCache cache, IHttpContextAccessor httpContextAccessor, AppSetting appSetting) : base(context, logger, cache, httpContextAccessor, appSetting)
    {
        _excelPackage = new ExcelPackage();
    }

    /// <summary>
    /// 从 excel 导入数据
    /// 需要 FileName(必须), SheetIndex(非必须) 和 SheetName(非必须) 参数
    /// 导入结果保存在 ExcelData 的 Data 字段, 错误信息保存在 Messages 字段
    /// get data, excel row and column index from 1
    /// </summary>
    /// <typeparam name="TEntitySet">业务数据类型</typeparam>
    /// <param name="excelData">excel 数据对象</param>
    public virtual void Import<TEntitySet>(ExcelData<TEntitySet> excelData) where TEntitySet : class, new()
    {
        if (string.IsNullOrWhiteSpace(excelData.FileName))
        {
            throw new Exception("excel 文件无法导入, 未提供要导入的文件名!");
        }

        var fileName = GetAttachmentPath(excelData.FileName);
        if (!File.Exists(fileName))
        {
            throw new Exception("文件不存在!");
        }

        var file = new FileInfo(fileName);
        if (file.Extension.ToLowerInvariant() != AppSettings.DEFAULT_EXCEL_FILE_EXTENSION)
        {
            throw new Exception($"只能导入 {AppSettings.DEFAULT_EXCEL_FILE_EXTENSION} 类型的 Excel 文件!");
        }

        _excelPackage.File = file;
        if (_excelPackage == null || _excelPackage.Workbook == null || _excelPackage.Workbook.Worksheets == null || _excelPackage.Workbook.Worksheets.Count == AppSettings.UNIT_ZERO)
        {
            throw new Exception("excel 文件读取失败, 不能读取空的 excel 文件!");
        }

        // read sheet
        using var sheet = string.IsNullOrWhiteSpace(excelData.SheetName) ? _excelPackage.Workbook.Worksheets[excelData.SheetIndex] : _excelPackage.Workbook.Worksheets[excelData.SheetName];
        if (sheet == null)
        {
            throw new Exception($"excel 文件读取失败, 无法读取 excel 表格: {excelData.SheetName ?? excelData.SheetIndex.ToString(CultureInfo.CurrentCulture)} !");
        }

        if (excelData.Attributes == null || (!excelData.Attributes.Any()))
        {
            throw new Exception($"excel 文件读取失败, 没有配置可以导入的列!");
        }

        if (sheet.Dimension.Columns <= AppSettings.UNIT_ZERO || sheet.Dimension.Rows <= AppSettings.UNIT_ZERO)
        {
            throw new Exception($"excel 文件读取失败, 文件中没有数据!");
        }

        excelData.FileName ??= _excelPackage.File.Name;
        excelData.SheetName ??= sheet.Name;
        var columns = ReadHeader(sheet, excelData);
        if ((excelData.Messages == null || (!excelData.Messages.Any())) && columns.Count > AppSettings.UNIT_ZERO && sheet.Dimension.Rows > AppSettings.UNIT_ONE)
        {
            ReadContent(sheet, excelData, columns);
        }
    }
    /// <summary>
    /// 使用文件名构造默认 excel 数据对象进行导入
    /// </summary>
    /// <typeparam name="TEntitySet">业务数据类型</typeparam>
    /// <param name="fileName">excel 文件名</param>
    public virtual void Import<TEntitySet>(string fileName) where TEntitySet : class, new() => Import(new ExcelData<TEntitySet> { FileName = fileName });
    /// <summary>
    /// 导入 excel 数据并在无错情况下直接入库保存
    /// </summary>
    /// <typeparam name="TEntitySet">业务数据类型</typeparam>
    /// <param name="excelData">excel 数据对象</param>
    /// <returns></returns>
    public virtual async Task<IActionResult> ImportAsync<TEntitySet>(ExcelData<TEntitySet> excelData) where TEntitySet : class, IEntitySet<TKey>, new()
    {
        Import(excelData);

        if (excelData.Messages != null && excelData.Messages.Any())
        {
            return new BadRequestObjectResult(excelData.GetErrorMessage());
        }

        if (excelData.Data != null && excelData.Data.Any())
        {
            var autoSaveChanges = AutoSaveChanges;
            AutoSaveChanges = false;
            foreach (var entitySet in excelData.Data)
            {
                await AddAsync(entitySet);
            }

            await SaveChangesAsync();
            AutoSaveChanges = autoSaveChanges;
        }

        return new OkResult();
    }
    /// <summary>
    /// 导入 excel 数据并在无错情况下直接入库保存
    /// </summary>
    /// <typeparam name="TEntitySet">业务数据类型</typeparam>
    /// <param name="fileName">excel 文件名</param>
    /// <returns></returns>
    public virtual async Task<IActionResult> ImportAsync<TEntitySet>(string fileName) where TEntitySet : class, IEntitySet<TKey>, new() => await ImportAsync(new ExcelData<TEntitySet> { FileName = fileName });

    /// <summary>
    /// 读取 excel 列头, 返回读出来的列头
    /// </summary>
    /// <typeparam name="TEntitySet"></typeparam>
    /// <param name="sheet"></param>
    /// <param name="attributes"></param>
    /// <returns></returns>
    private static Dictionary<int, string> ReadHeader<TEntitySet>(in ExcelWorksheet sheet, ExcelData<TEntitySet> excelData)
    {
        var attributes = excelData.Attributes;
        var errors = new Dictionary<int, string>();
        var columns = new Dictionary<int, string>();
        for (int index = AppSettings.UNIT_ZERO; index < sheet.Dimension.Columns; index++)
        {
            var header = (sheet.Cells[AppSettings.UNIT_ONE, index + AppSettings.UNIT_ONE].GetValue<string>() ?? string.Empty).Trim();
            var attribute = attributes.FirstOrDefault(x => x.Name == header);
            if (attribute != null && attribute.Field != null)
            {
                columns.Add(index, attribute.Field.Name);
            }
        }

        var columnIndex = sheet.Dimension.Columns + 1;
        foreach (var attribute in attributes.Where(x => x.Required).ToList())
        {
            if (attribute.Field != null && !columns.ContainsValue(attribute.Field.Name))
            {
                errors.Add(columnIndex++, $"缺少必须导入的 <strong>{attribute.Name}</strong> 列!");
            }
        }

        excelData.Messages = errors;
        return columns;
    }
    /// <summary>
    /// 读取 excel 的内容
    /// </summary>
    /// <typeparam name="TEntitySet"></typeparam>
    /// <param name="sheet"></param>
    /// <param name="excelData"></param>
    /// <param name="columns"></param>
    private static void ReadContent<TEntitySet>(in ExcelWorksheet sheet, ExcelData<TEntitySet> excelData, Dictionary<int, string> columns) where TEntitySet : class, new()
    {
        // get data, excel row and column index from 1
        var list = new List<TEntitySet>();
        var errors = new Dictionary<int, string>();
        var attributes = excelData.Attributes;
        for (int row = AppSettings.UNIT_TWO; row <= sheet.Dimension.Rows; row++)
        {
            // read value
            var t = new TEntitySet();
            var messages = new List<string>();
            for (int col = AppSettings.UNIT_ONE; col <= sheet.Dimension.Columns; col++)
            {
                if (!columns.ContainsKey(col - AppSettings.UNIT_ONE))
                {
                    continue;
                }

                var field = columns[col - AppSettings.UNIT_ONE];
                var attribute = attributes.FirstOrDefault(x => x.Field?.Name == field);
                if (attribute != null && attribute.Field != null && attribute.FieldType != null)
                {
                    try
                    {
                        object? value;
                        var cell = sheet.Cells[row, col];

                        try
                        {
                            cell.InvokeGenericMethod(nameof(cell.GetValue), null, out value, attribute.FieldType);
                        }
                        catch (Exception)
                        {
                            value = cell.Value;
                        }

                        if (attribute.Required && value == null)
                        {
                            throw new Exception("不能为空!");
                        }

                        if (value != null)
                        {
                            if (attribute.FieldType == typeof(string) && value is string stringValue && attribute.MaxLength > 0 && stringValue.Length > attribute.MaxLength)
                            {
                                throw new Exception($"输入超过最大长度 {attribute.MaxLength} !");
                            }

                            if (attribute.FieldType != value.GetType().GetCoreType())
                            {
                                value = GetPropertyValue(attribute, value);
                            }
                        }

                        if (value != null)
                        {
                            // excel cell delegate method call
                            if ((!string.IsNullOrWhiteSpace(attribute.Field.Name)) && excelData.Converters != null && excelData.Converters.ContainsKey(attribute.Field.Name))
                            {
                                //自定义设置方式
                                excelData.Converters[attribute.Field.Name]?.Invoke(t, value);
                            }
                            else
                            {
                                //默认设置方式
                                var setValue = (value is IConvertible) ? Convert.ChangeType(value, attribute.FieldType, CultureInfo.CurrentCulture) : value;
                                if (attribute.Field.MemberType == MemberTypes.Property)
                                {
                                    if (attribute.Field is PropertyInfo propertyInfo && propertyInfo.CanWrite)
                                    {
                                        propertyInfo.SetValue(t, setValue);
                                    }
                                    else if (attribute.Field is FieldInfo fieldInfo)
                                    {
                                        fieldInfo.SetValue(t, setValue);
                                    }
                                }
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        messages.Add($"<strong>{attribute.Name}</strong> {ex.Message}");
                    }
                }
            }

            // check repeat
            if (list.Contains(t, excelData.Comparer))
            {
                messages.Add("与其他行重复!");
            }

            // check for whole row
            excelData.Validator?.Invoke(t, list);

            list.Add(t);

            // process error message
            if (messages.IsNotNullOrEmpty())
            {
                errors.Add(row, string.Join(AppSettings.CHAR_SEMICOLON.ToString(), messages));
            }
        }

        excelData.Messages = errors;
        excelData.Data = list;
    }
    /// <summary>
    /// 获取单元格内容
    /// </summary>
    /// <param name="attribute"></param>
    /// <param name="value"></param>
    /// <returns></returns>
    private static object? GetPropertyValue(ExcelAttribute attribute, object value)
    {
        var strValue = (value.ToString() ?? string.Empty).Trim();
        var result = (value is string) ? strValue : value;
        switch (Type.GetTypeCode(attribute.FieldType))
        {
            case TypeCode.Boolean:
                if (value is not bool)
                {
                    result = (strValue == "是" || strValue == "否") ? (strValue == "是") : throw new Exception("只能填\"是\"或\"否\"!");
                }
                break;
            case TypeCode.DateTime:
                if (value is double doubleValue)
                {
                    result = DateTime.FromOADate(doubleValue);
                }
                break;
            default:
                if (attribute.FieldType?.IsEnum ?? false)
                {
                    result = attribute.FieldType.GetField(strValue)?.GetValue(null);
                    if (result == null)
                    {
                        throw new Exception("格式不正确!");
                    }
                }

                break;
        }

        return result;
    }
}

/// <summary>
/// excel 导入导出操作的服务
/// </summary>
/// <typeparam name="TDbContext">数据库上下文</typeparam>
/// <typeparam name="TKey">键类型</typeparam>
public class ExcelImportService<TDbContext> : ExcelImportService<TDbContext, string> where TDbContext : DbContext
{
    public ExcelImportService(TDbContext context, ILogger<ExcelImportService<TDbContext, string>> logger, IDistributedCache cache, IHttpContextAccessor httpContextAccessor, AppSetting appSetting) : base(context, logger, cache, httpContextAccessor, appSetting) { }
}